﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using E.Model;
using X.DALSqlServer;

namespace E.DAL
{
    public class PublicCourseDAL
    {
        /// <summary>
        /// 添加公共课程
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddPublicCourse(PublicCourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@Description", model.Description),
                new SqlParameter("@IsOriginal", model.IsOriginal)
            };
            string sql = "INSERT INTO E_Public_Course(Name,SchoolHour,Description,IsOriginal)VALUES(@Name,@SchoolHour,@Description,@IsOriginal)";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取简单公共课程列表
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetSimplePublicCourseList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Public_Course";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取简单公共课程列表（非原有课程）
        /// </summary>
        /// <returns></returns>
        public IDictionary<int, string> GetSimpleNonOriginalPublicCourseList()
        {
            IDictionary<int, string> list = new Dictionary<int, string>();
            string sql = "SELECT ID, Name FROM E_Public_Course WHERE IsOriginal = 0";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    list.Add((int)dr[0], dr[1].ToString());
                }
                return list;
            }
        }

        /// <summary>
        /// 获取公共课程列表
        /// </summary>
        /// <returns></returns>
        public IList<PublicCourseModel> GetPublicCourseList()
        {
            IList<PublicCourseModel> list = new List<PublicCourseModel>();
            string sql = "SELECT a.ID, a.Name, a.SchoolHour, a.IsOriginal, COUNT(b.UserID) AS UserCount FROM E_Public_Course AS a LEFT OUTER JOIN E_Public_Course2User AS b ON a.ID = b.CourseID GROUP BY a.ID, a.Name, a.SchoolHour, a.IsOriginal";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    PublicCourseModel model = new PublicCourseModel();
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.SchoolHour = (decimal)dr["SchoolHour"];
                    model.UserCount = (int)dr["UserCount"];
                    model.IsOriginal = (bool)dr["IsOriginal"];
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 检查课程名称是否重复
        /// </summary>
        /// <param name="coursename"></param>
        /// <param name="selfid"></param>
        /// <returns></returns>
        public bool CheckExistCourseName(string name, int selfid)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@Name", name),
                new SqlParameter("@SelfID", selfid)
            };
            string sql = "SELECT ID FROM E_Public_Course WHERE Name = @Name ";
            if (selfid != -1)
            {
                sql += " AND ID <> @SelfID";
            }
            return SqlHelper.Exists(sql, parms);
        }

        /// <summary>
        /// 获取公共课程名称
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PublicCourseModel GetPublicCourseInfoById(int id)
        {
            PublicCourseModel model = new PublicCourseModel();
            string sql = "SELECT ID, Name, SchoolHour, Description, IsOriginal FROM E_Public_Course WHERE ID = " + id;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.Name = dr["Name"].ToString();
                    model.SchoolHour = (decimal)dr["SchoolHour"];
                    model.Description = dr["Description"].ToString();
                    model.IsOriginal = (bool)dr["IsOriginal"];
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 更新公共课程
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdatePublicCourse(PublicCourseModel model)
        {
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@Name", model.Name),
                new SqlParameter("@SchoolHour", model.SchoolHour),
                new SqlParameter("@Description", model.Description)
            };
            string sql = "UPDATE E_Public_Course SET Name = @Name, SchoolHour = @SchoolHour, Description = @Description WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取公共课程学员列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public PublicUserInCourseListModel GetUserInCourseListByCondition(PublicUserInCourseSearchModel searchModel, int pageSize, int pageIndex)
        {
            PublicUserInCourseListModel listModel = new PublicUserInCourseListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@UserName", searchModel.UserName),
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<UserListModel> list = new List<UserListModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getUserInCourseListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        UserListModel shortModel = new UserListModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.LoginID = dr["LoginID"].ToString();
                        shortModel.UserName = dr["UserName"].ToString();
                        shortModel.Email = dr["Email"].ToString();
                        shortModel.Gender = (bool)dr["Gender"];
                        shortModel.SignedCertificateName = dr["CertificateName"].ToString();
                        shortModel.DelFlag = (bool)dr["DelFlag"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 公共课程报名
        /// </summary>
        /// <param name="courseid"></param>
        /// <param name="userid"></param>
        /// <returns></returns>
        public int SignUp(int courseid, int userid)
        {
            string sql = "SELECT ID FROM E_Public_Course2User WHERE CourseID = " + courseid + " AND UserID = " + userid;
            bool exist = SqlHelper.Exists(sql);
            if (!exist)
            {
                return SqlHelper.ExecuteNonQueryBySql("INSERT INTO E_Public_Course2User(CourseID,UserID)VALUES(" + courseid + "," + userid + "); INSERT INTO E_Public_StudyLog(UserID,CourseID,Chapters,Progress)VALUES(" + userid + "," + courseid + ",'',0);");
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 获取我的公共课程列表
        /// </summary>
        /// <returns></returns>
        public IList<PublicStudyLogModel> GetMyPublicCourseList(int userid)
        {
            IList<PublicStudyLogModel> list = new List<PublicStudyLogModel>();
            string sql = "SELECT b.ID AS CourseID, b.Name AS CourseName, b.SchoolHour, b.IsOriginal, c.Progress FROM E_Public_Course2User AS a INNER JOIN E_Public_Course AS b ON a.CourseID = b.ID INNER JOIN E_Public_StudyLog AS c ON a.CourseID = c.CourseID WHERE a.UserID = " + userid;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    PublicStudyLogModel model = new PublicStudyLogModel();
                    model.CourseID = (int)dr["CourseID"];
                    model.CourseName = dr["CourseName"].ToString();
                    model.SchoolHour = (decimal)dr["SchoolHour"];
                    model.IsOriginal = (bool)dr["IsOriginal"];
                    model.Progress = (int)dr["Progress"];
                    list.Add(model);
                }
                return list;
            }
        }

        #region 公共课程课件

        /// <summary>
        /// 获取课件信息
        /// </summary>
        /// <param name="courseWareId"></param>
        /// <returns></returns>
        public PublicCourseWareModel GetPublicCourseWareInfoById(int courseWareId)
        {
            PublicCourseWareModel model = new PublicCourseWareModel();
            string sql = "SELECT ID,CourseID,SerialNumber,Content,CourseName,ImgUrl,SoundUrl,VideoUrl FROM v_internal_publiccourseware WHERE ID = " + courseWareId;
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                if (dr.Read())
                {
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.Content = dr["Content"].ToString();
                    model.CourseName = dr["CourseName"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                }
                else
                    model.ID = -1;

                return model;
            }
        }

        /// <summary>
        /// 获取课件信息（列表）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public IList<PublicCourseWareModel> GetPublicCourseWareInfoById(string[] id)
        {
            IList<PublicCourseWareModel> list = new List<PublicCourseWareModel>();
            string sql = "SELECT ID,CourseID,SerialNumber,ImgUrl,SoundUrl,VideoUrl FROM E_Public_CourseWare WHERE " + DALUtils.GetInCondition("ID", id) + " ORDER BY SerialNumber DESC";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    PublicCourseWareModel model = new PublicCourseWareModel();
                    model.ID = (int)dr["ID"];
                    model.CourseID = (int)dr["CourseID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 添加课件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddPublicCourseWare(PublicCourseWareModel model)
        {
            IDbDataParameter[] parms = new IDbDataParameter[] { 
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@SerialNumber", model.SerialNumber),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@VideoUrl", model.VideoUrl),
            };
            return SqlHelper.ExecuteNonQueryByProcecdure("sp_internal_addPublicCourseWare", parms);
        }

        /// <summary>
        /// 更新课件
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int UpdatePublicCourseWare(PublicCourseWareModel model)
        {
            IDbDataParameter[] parms = new IDbDataParameter[] { 
                new SqlParameter("@ID", model.ID),
                new SqlParameter("@CourseID", model.CourseID),
                new SqlParameter("@SerialNumber", model.SerialNumber),
                new SqlParameter("@OriginalSerialNumber", model.OriginalSerialNumber),
                new SqlParameter("@Content", model.Content),
                new SqlParameter("@ImgUrl", model.ImgUrl),
                new SqlParameter("@SoundUrl", model.SoundUrl),
                new SqlParameter("@VideoUrl", model.VideoUrl),
            };
            string sql = "";

            if (model.SerialNumber >= model.OriginalSerialNumber)
            {
                if (model.SerialNumber > model.OriginalSerialNumber)
                {
                    sql += "UPDATE E_Public_CourseWare SET SerialNumber = SerialNumber - 1 WHERE CourseID = @CourseID AND SerialNumber <= @SerialNumber AND SerialNumber > @OriginalSerialNumber AND ID <> @ID;";
                }
            }
            else
            {
                sql += "UPDATE E_Public_CourseWare SET SerialNumber = SerialNumber + 1 WHERE CourseID = @CourseID AND SerialNumber Between @SerialNumber AND @OriginalSerialNumber AND ID <> @ID;";
            }

            sql += " UPDATE E_Public_CourseWare SET CourseID = @CourseID, SerialNumber = @SerialNumber, Content = @Content, ImgUrl = @ImgUrl, SoundUrl = @SoundUrl, VideoUrl = @VideoUrl WHERE ID = @ID";
            return SqlHelper.ExecuteNonQueryBySql(sql, parms);
        }

        /// <summary>
        /// 获取课件列表
        /// </summary>
        /// <param name="searchModel"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public PublicCourseWareListModel GetPublicCourseWareListByCondition(PublicCourseWareSearchModel searchModel, int pageSize, int pageIndex)
        {
            PublicCourseWareListModel listModel = new PublicCourseWareListModel();
            IDataParameter[] parms = new IDataParameter[] { 
                new SqlParameter("@CourseID", searchModel.CourseID),
                new SqlParameter("@PageSize", pageSize),
                new SqlParameter("@PageIndex", pageIndex)
            };
            IList<ShortPublicCourseWareModel> list = new List<ShortPublicCourseWareModel>();
            using (IDataReader dr = SqlHelper.ExecuteDataReaderByStoredProc("sp_internal_getPublicCourseWareListByCondition", parms))
            {
                int totalCount = 0;
                while (dr.Read())
                {
                    if (dr["ID"] == DBNull.Value)
                    {
                        totalCount = Convert.ToInt32(dr["pos"]);
                    }
                    else
                    {
                        ShortPublicCourseWareModel shortModel = new ShortPublicCourseWareModel();
                        shortModel.ID = (int)dr["ID"];
                        shortModel.CourseName = dr["CourseName"].ToString();
                        shortModel.SerialNumber = (int)dr["SerialNumber"];
                        list.Add(shortModel);
                    }
                }
                listModel.TotalCount = totalCount;
                listModel.List = list;
                return listModel;
            }
        }

        /// <summary>
        /// 获取课件列表
        /// </summary>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public IList<PublicCourseWareModel> GetPublicCourseWareListByCourseID(int courseid)
        {
            IList<PublicCourseWareModel> list = new List<PublicCourseWareModel>();
            string sql = "SELECT ID, SerialNumber, Content, ImgUrl, SoundUrl, VideoUrl FROM E_CourseWare WHERE CourseID = " + courseid + " ORDER BY SerialNumber, ID";
            using (IDataReader dr = SqlHelper.ExecuteDataReaderBySql(sql))
            {
                while (dr.Read())
                {
                    PublicCourseWareModel model = new PublicCourseWareModel();
                    model.ID = (int)dr["ID"];
                    model.SerialNumber = (int)dr["SerialNumber"];
                    model.Content = dr["Content"].ToString();
                    model.ImgUrl = dr["ImgUrl"].ToString();
                    model.SoundUrl = dr["SoundUrl"].ToString();
                    model.VideoUrl = dr["VideoUrl"].ToString();
                    list.Add(model);
                }
                return list;
            }
        }

        /// <summary>
        /// 删除课件
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DelPublicCourseWare(int id)
        {
            string sql = "DELETE FROM PublicCourseWareModel WHERE ID = " + id;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        /// <summary>
        /// 更新页码（删除某一页时）
        /// </summary>
        /// <param name="startNumber"></param>
        /// <param name="courseid"></param>
        /// <returns></returns>
        public int UpdateSerialNumber(int startNumber, int courseid)
        {
            string sql = "UPDATE PublicCourseWareModel SET SerialNumber = SerialNumber - 1 WHERE CourseID = " + courseid + " AND SerialNumber > " + startNumber;
            return SqlHelper.ExecuteNonQueryBySql(sql);
        }

        #endregion
    }
}
